import re
import pymysql

if __name__ == '__main__':
      pat = 'id: "(.*?)"'
      conn = pymysql.Connect(host = 'localhost',
                             port = 3306,
                             user = 'root',
                             passwd = 'root',
                             db = 'spark',
                             charset='utf8')
      cur = conn.cursor()
      s=open("C:\\Users\\diao\\Desktop\\1111.txt","r",encoding='utf-8')
      sql="select * from user "
      cur.execute(sql)
      #result是一个元组集合
      result=cur.fetchall()
      print(len(result))
      names=[]
      for line in s:
            pat1=r'.*'+line.replace('\n', '')+'.*'
            #pat1=line.replace('\n', '')
            # pat2=line.replace('\n', '')+'.*'
            # pat3='.*'+line.replace('\n', '')
            #遍历user表
            for index in range(len(result)):
                  name=result[index][2]
                  #print(name)
                  if re.findall(pat1,name):
                        names.append(name)
                        into="INSERT INTO user_copy1(`GuestNum`,`用户编号`,`单位名称`,`别名`,`省份`,`城市`,`行业大类`,`行业中类`,`行业子类`,`隶属部门`) VALUES (%s,%s, %s, %s,%s,%s, %s, %s,%s,%s)"
                        value=(result[index][0],result[index][1],result[index][2],line.replace('\n', ''),result[index][3],result[index][4],result[index][5],result[index][6],result[index][7],result[index][8])
                        cur.execute(into,value)
                        conn.commit()
                  # elif re.findall(pat2,name):
                  #       names.append(name)
                  # elif re.findall(pat3,name):
                  #       names.append(name)
      # result_list=[]
      # for name in names:
      #       sql1="select * from user where `单位名称` =" +"'"+name+"'";
      #       cur.execute(sql1)
      #       #result是一个元组集合
      #       result1=cur.fetchall()
      #       result_list.append(result1)
      # into="INSERT INTO user_tmp(`客户编号`,`用户编号`,`单位名称`,`省份`,`城市`,`行业大类`,`行业中类`,`行业子类`,`隶属部门`) VALUES (%s,%s, %s, %s,%s,%s, %s, %s,%s)"
      # cur.executemany(into,result_list)
      # conn.commit()
      cur.close()
      conn.close()
